package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.GridGerenEntity;
import br.com.skytef.entity.LocalEntity;
import br.com.skytef.entity.ProductMerchandiseEntity;
import br.com.skytef.entity.UserEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class GerenciamentoDao {
	public Connection connection;

	public GerenciamentoDao() {
		this.connection = new ConnectionFactory().getConnection();
	}

	public GerenciamentoDao(HttpSession session) {
		this.connection = utils.recoverSessionDBConnection(session);
	}

	public GerenciamentoDao(Connection con) {
		this.connection = con;
	}

	public Connection getConnection() {
		return connection;
	}
	/*-------------------------------------------------------------------------
	 * 
	 -------------------------------------------------------------------------*/
	/*-------------------------------------------------------------------------
	 * 
	 -------------------------------------------------------------------------*/
	public List<GridGerenEntity> selectDadosGrid() throws Exception{
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectGrid());		
		stmt.setString(1, "ativo");
		List<GridGerenEntity> result = selectExec(stmt);
		connection.close();
		return result;		
	}
	
	public List<GridGerenEntity> selectDadosGridByCpf(String cpf) throws Exception{
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectGridByCpf());
		stmt.setString(1, cpf);
		stmt.setString(2, "ativo");
		List<GridGerenEntity> result = selectExec(stmt);
		connection.close();
		return result;		
	}
	
	
	private List<GridGerenEntity> selectExec(PreparedStatement stmt) throws Exception {		
		try {
			List<GridGerenEntity> grids = new ArrayList<GridGerenEntity>();						
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				GridGerenEntity grid = new GridGerenEntity();
				
					grid.setNumerocomanda(rs.getInt("numerocomanda")); 
					grid.setCpf(rs.getString("cpf"));
					grid.setNome(rs.getString("nome")); 
					grid.setIdpedido(rs.getInt("idpedido")); 
					grid.setDatacriacao(rs.getDate("datacriacao")); 
					grid.setHoracriacao(rs.getTime("horacriacao"));
					grid.setVlrCredito(rs.getFloat("valorCredito"));
					grid.setNumero(rs.getString("numero")); 
					grid.setSituacao(rs.getString("situacao"));
					grids.add(grid);				
			}
			rs.close();			
			return grids;			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	
public GridGerenEntity getCardCli(String cli, int idPedido) throws Exception {
		
	GridGerenEntity grid = new GridGerenEntity();
	Connection connection = new ConnectionFactory().getConnection();
		
		try {
			connection.setAutoCommit(false);
			PreparedStatement stmt = connection.prepareStatement(this.getQrySelectCliCard());
			stmt.setString(1, cli);
			stmt.setInt(2, idPedido);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				do {
					grid = new GridGerenEntity();
					grid.setNumerocomanda(rs.getInt("numerocomanda")); 
					grid.setCpf(rs.getString("cpf"));
					grid.setNome(rs.getString("nome"));
					grid.setTelefone(rs.getString("telefone"));
					grid.setIdpedido(rs.getInt("idpedido")); 
					grid.setDatacriacao(rs.getDate("datacriacao")); 
					grid.setHoracriacao(rs.getTime("horacriacao"));
					grid.setNumero(rs.getString("numero")); 
					grid.setSituacao(rs.getString("situacao"));
				
				} while (rs.next());
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("SQL erro: " + e);
		}
		return grid;
	}
	
	

	public GridGerenEntity getPedidoById(int idPedido) throws SQLException {
		GridGerenEntity comanda = new GridGerenEntity();
		Connection connection = new ConnectionFactory().getConnection();
		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery("SELECT ped_pad.numerocomanda, valorCredito, cli.cpf, cli.nome, ped.idpedido,  "
					+ "ped.datacriacao, ped.horacriacao, ped.valorCredito, car.numero, car.ativo, car.bloqueado "
					+ "FROM pedidos_has_clientes base LEFT JOIN pedidos ped ON base.fk_pedido = ped.idpedido " 
					+ "LEFT JOIN pedidos_padrao ped_pad ON ped_pad.pedidos_idpedido = ped.idpedido " 
					+ "LEFT JOIN clientes cli ON cli.idclientes = base.fk_cliente "       
					+ "LEFT JOIN cartoes car ON car.idcartao = base.fk_cartao ");
			//+ "WHERE  ped.idPedido = "+ idPedido + ";");

			if (rs.next()) {
				do {
					comanda = new GridGerenEntity();
					comanda.setNumerocomanda(rs.getInt("numerocomanda"));
					comanda.setVlrCredito(rs.getFloat("valorCredito"));
					comanda.setCpf(rs.getString("cpf"));
					comanda.setNome(rs.getString("nome")); 
					comanda.setIdpedido(rs.getInt("idpedido")); 
					comanda.setDatacriacao(rs.getDate("datacriacao")); 
					comanda.setHoracriacao(rs.getTime("horacriacao"));
					comanda.setVlrCredito(rs.getFloat("valorCredito"));
					comanda.setNumero(rs.getString("numero")); 
					comanda.setAtivo(rs.getBoolean("ativo")); 
					comanda.setBloqueado(rs.getBoolean("bloqueado"));

				} while (rs.next());
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			System.out.println("SQL erro: " + e);
		}
		return comanda;
	}
	/*
	 * Query
	 */

	private String getQrySelectGrid() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT ped_pad.numerocomanda, ");
		sql.append("	   valorCredito, ");
		sql.append("       cli.cpf, "); 
		sql.append("       cli.nome, "); 
		sql.append("       ped.idpedido, "); 
		sql.append("       ped.datacriacao, "); 
		sql.append("       ped.horacriacao, ");
		sql.append("       car.numero, "); 
		sql.append("       car.idCartao, ");
		sql.append("       car.situacao "); ; 
		sql.append("FROM   pedidos_has_clientes base "); 
		sql.append("       LEFT JOIN pedidos ped "); 
		sql.append("              ON base.fk_pedido = ped.idPedido "); 
		sql.append("       LEFT JOIN pedidos_padrao ped_pad "); 
		sql.append("              ON ped_pad.Pedidos_idPedido = ped.idPedido "); 
		sql.append("       LEFT JOIN clientes cli "); 
		sql.append("              ON cli.idclientes = base.fk_cliente "); 
		sql.append("       LEFT JOIN cartoes car "); 
		sql.append("              ON car.idcartao = base.fk_cartao "); 
		sql.append("WHERE  car.situacao = ? ;"); 

		return sql.toString();
	}
	
	private String getQrySelectGridByCpf() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT ped_pad.numerocomanda, ");
		sql.append("	   valorCredito, ");
		sql.append("       cli.cpf, "); 
		sql.append("       cli.nome, "); 
		sql.append("       ped.idpedido, "); 
		sql.append("       ped.datacriacao, "); 
		sql.append("       ped.horacriacao, "); 
		sql.append("       car.numero, "); 
		sql.append("       car.idCartao, ");
		sql.append("       car.situacao "); ; 
		sql.append("FROM   pedidos_has_clientes base "); 
		sql.append("       LEFT JOIN pedidos ped "); 
		sql.append("              ON base.fk_pedido = ped.idPedido "); 
		sql.append("       LEFT JOIN pedidos_padrao ped_pad "); 
		sql.append("              ON ped_pad.Pedidos_idPedido = ped.idPedido "); 
		sql.append("       LEFT JOIN clientes cli "); 
		sql.append("              ON cli.idclientes = base.fk_cliente "); 
		sql.append("       LEFT JOIN cartoes car "); 
		sql.append("              ON car.idcartao = base.fk_cartao "); 
		sql.append("WHERE  cli.cpf = ? and car.situacao = ? "); 

		return sql.toString();
	}
	
	private String getQrySelectCliCard() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT ped_pad.numerocomanda, ");
		sql.append("	   valorCredito, ");
		sql.append("       cli.cpf, "); 
		sql.append("       cli.nome, ");
		sql.append("       cli.telefone, ");
		sql.append("       ped.idpedido, "); 
		sql.append("       ped.datacriacao, "); 
		sql.append("       ped.horacriacao, ");
		sql.append("       car.numero, "); 
		sql.append("       car.situacao "); ; 
		sql.append("FROM   pedidos_has_clientes base "); 
		sql.append("       LEFT JOIN pedidos ped "); 
		sql.append("              ON base.fk_pedido = ped.idPedido "); 
		sql.append("       LEFT JOIN pedidos_padrao ped_pad "); 
		sql.append("              ON ped_pad.Pedidos_idPedido = ped.idPedido "); 
		sql.append("       LEFT JOIN clientes cli "); 
		sql.append("              ON cli.idclientes = base.fk_cliente "); 
		sql.append("       LEFT JOIN cartoes car "); 
		sql.append("              ON car.idcartao = base.fk_cartao  "); 
		sql.append("WHERE  cli.cpf = ? and ped.idpedido = ? ; "); 

		return sql.toString();
	}

}
